{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "yRU6wls7gcBc"
   },
   "source": [
    "# Spark SQL 基础\n",
    "<table align=\"left\">\n",
    "  <td>\n",
    "    <a target=\"_blank\" href=\"http://nbviewer.ipython.org/github/ShowMeAI-Hub/awesome-AI-cheatsheets/blob/main/Spark/Spark_SQL_cheatsheet_code.ipynb\"><img src=\"https://raw.githubusercontent.com/jupyter/design/master/logos/Badges/nbviewer_badge.svg\" />在nbviewer上查看notebook</a>\n",
    "  </td>\n",
    "  <td>\n",
    "    <a target=\"_blank\" href=\"https://colab.research.google.com/github/ShowMeAI-Hub/awesome-AI-cheatsheets/blob/main/Spark/Spark_SQL_cheatsheet_code.ipynb\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" />在Google Colab运行</a>\n",
    "  </td>\n",
    "  <td>\n",
    "    <a target=\"_blank\" href=\"https://github.com/ShowMeAI-Hub/awesome-AI-cheatsheets/tree/main/Spark/Spark_SQL_cheatsheet_code.ipynb\"><img src=\"https://badgen.net/badge/open/github/color=cyan?icon=github\" />在Github上查看源代码</a>\n",
    "  </td>\n",
    "  <td>\n",
    "    <a target=\"_blank\" href=\"https://github.com/ShowMeAI-Hub/awesome-AI-cheatsheets/Spark/Spark_SQL速查表.pdf\"><img src=\"https://badgen.net/badge/download/pdf/color=white?icon=github\"/>下载速查表</a>\n",
    "  </td>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 说明\n",
    "**notebook by [韩信子](https://github.com/HanXinzi-AI)@[ShowMeAI](https://github.com/ShowMeAI-Hub)**\n",
    "\n",
    "更多AI速查表资料请查看[速查表大全](https://github.com/ShowMeAI-Hub/awesome-AI-cheatsheets)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "9ogI_OOlgcBd"
   },
   "source": [
    "Spark SQL 是 Apache Spark 处理结构化数据的模块。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "hsc3xfDPgcBd"
   },
   "source": [
    "## 配置spark环境"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "id": "wX5_o7ewgcBe"
   },
   "outputs": [],
   "source": [
    "!apt-get install openjdk-8-jdk-headless -qq > /dev/null\n",
    "!wget -q www-us.apache.org/dist/spark/spark-2.4.8/spark-2.4.8-bin-hadoop2.7.tgz  \n",
    "!tar xf spark-2.4.8-bin-hadoop2.7.tgz\n",
    "!pip install -q findspark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "id": "M46kECdegcBe"
   },
   "outputs": [],
   "source": [
    "import os\n",
    "os.environ[\"JAVA_HOME\"]=\"/usr/lib/jvm/java-8-openjdk-amd64\"\n",
    "os.environ[\"SPARK_HOME\"]=\"/content/spark-2.4.8-bin-hadoop2.7\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "id": "956QIaiOgcBe"
   },
   "outputs": [],
   "source": [
    "import findspark\n",
    "findspark.init()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "C5z9i5y7gcBf"
   },
   "source": [
    "## 初始化SparkSession"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "3pl4OSbHgcBf"
   },
   "source": [
    "**初始化**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "R5hoqJ5WgcBg"
   },
   "outputs": [],
   "source": [
    " SparkSession 用于创建数据帧，将数据帧注册为表，执行 SQL 查询，缓存表及读取 Parquet 文件。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "id": "3-UgcWZQgcBg"
   },
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "id": "3A6wOhyPgcBg"
   },
   "outputs": [],
   "source": [
    "spark = SparkSession \\\n",
    "       .builder \\\n",
    "       .appName(\"Python Spark SQL basic example\") \\\n",
    "       .config(\"spark.some.config.option\", \"some-value\") \\\n",
    "       .getOrCreate()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "wxCMwOKUgcBg"
   },
   "source": [
    "## 创建数据帧"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "2RxiNDEggcBh"
   },
   "source": [
    "### 从 RDD 创建"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "id": "CIfvJvsJgcBh"
   },
   "outputs": [],
   "source": [
    "from pyspark.sql.types import *\n",
    "from pyspark.sql import *\n",
    "from pyspark.sql.functions import *"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "X8a1CotKgcBh"
   },
   "source": [
    "**推断 Schema**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "id": "G3_1q11AgcBh"
   },
   "outputs": [],
   "source": [
    "sc = spark.sparkContext"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "I0SSaMwNhjEk"
   },
   "outputs": [],
   "source": [
    "!wget https://github.com/awesome-AI-cheatsheets/tree/main/Spark/data/people.txt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "id": "jPHFQNBSgcBi"
   },
   "outputs": [],
   "source": [
    "lines = sc.textFile(\"people.txt\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "id": "qaTLZHmugcBi"
   },
   "outputs": [],
   "source": [
    "parts = lines.map(lambda l: l.split(\",\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "id": "zwjtWotogcBi"
   },
   "outputs": [],
   "source": [
    "people = parts.map(lambda p: Row(name=p[0],age=int(p[1])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "id": "tZ2KMLyugcBi"
   },
   "outputs": [],
   "source": [
    "peopledf = spark.createDataFrame(people)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "4tfXHXd3gcBi"
   },
   "source": [
    "**指定 Schema**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "id": "naEyU-r4gcBj"
   },
   "outputs": [],
   "source": [
    "people = parts.map(lambda p: Row(name=p[0], age=int(p[1].strip())))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "id": "6_s2y9bogcBj"
   },
   "outputs": [],
   "source": [
    "schemaString = \"name age\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "id": "6CkIVev3gcBj"
   },
   "outputs": [],
   "source": [
    "fields = [StructField(field_name, StringType(), True) for field_name in schemaString.split()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "id": "W14KO6GRgcBj"
   },
   "outputs": [],
   "source": [
    "schema = StructType(fields)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "QGYe9QHAgcBj",
    "outputId": "673e39f8-ef0a-432e-8e7b-d20caba704a2"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+---+\n",
      "|   name|age|\n",
      "+-------+---+\n",
      "|Michael| 29|\n",
      "|   Andy| 30|\n",
      "| Justin| 19|\n",
      "+-------+---+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.createDataFrame(people, schema).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "7AgEbiwqgcBk"
   },
   "source": [
    "### 从Spark数据源创建"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "b23Yj7sKgcBk"
   },
   "source": [
    "**JSON**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "id": "Euhtc2dxiR07"
   },
   "outputs": [],
   "source": [
    "!wget -q https://github.com/awesome-AI-cheatsheets/tree/main/Spark/data/people.json\n",
    "!wget -q https://github.com/awesome-AI-cheatsheets/tree/main/Spark/data/employees.json\n",
    "!wget -q https://github.com/awesome-AI-cheatsheets/tree/main/Spark/data/customers.json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "id": "Cu3rpicvgcBk"
   },
   "outputs": [],
   "source": [
    "df = spark.read.json(\"customers.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "m_WjMy_agcBk",
    "outputId": "8543af06-fe67-4230-f196-8311424660c3"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+---+---------+----------+--------------------+\n",
      "|             address|age|firstName|  lastName|         phoneNumber|\n",
      "+--------------------+---+---------+----------+--------------------+\n",
      "|[New Orleans, LA,...| 22|     Jane|Butterburg|[210-332-578, mob...|\n",
      "|[Brighton, MI, 4 ...| 25|Josephine|   Darakjy|[210-319-103, mob...|\n",
      "|[Bridgeport, NJ, ...| 32|    Boris|    Chemel|[210-322-007, mob...|\n",
      "|[Bridgeport, NJ, ...| 35|  Johnson|     Smith|[210-303-029, mob...|\n",
      "+--------------------+---+---------+----------+--------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "id": "l6MG5NSKgcBk"
   },
   "outputs": [],
   "source": [
    "df2 = spark.read.load(\"people.json\", format=\"json\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "YYtYX6tYgcBk"
   },
   "source": [
    "**Parquet文件**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "id": "5i-sh_HZiyyA"
   },
   "outputs": [],
   "source": [
    "!wget -q https://github.com/apache/spark/raw/master/examples/src/main/resources/users.parquet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "id": "Vlhj5AOjgcBl"
   },
   "outputs": [],
   "source": [
    "df3 = spark.read.load(\"users.parquet\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "bKSFGZQggcBl"
   },
   "source": [
    "**文本文件**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "id": "cdtUbBsogcBl"
   },
   "outputs": [],
   "source": [
    "df4 = spark.read.text(\"people.txt\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "nl1-0nDAgcBl"
   },
   "source": [
    "## 查阅数据信息"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "rqN1H6FFgcBl"
   },
   "source": [
    "**查阅spark Dataframe的信息**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "Ir2B_o9UgcBl",
    "outputId": "f02b914a-86ee-410a-d7b8-0922cfe05f63"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('address', 'struct<city:string,state:string,street:string,zip:string>'),\n",
       " ('age', 'bigint'),\n",
       " ('firstName', 'string'),\n",
       " ('lastName', 'string'),\n",
       " ('phoneNumber', 'struct<number:string,type:string>')]"
      ]
     },
     "execution_count": 70,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes   #返回 df 的列名与数据类型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "QWmlPWKegcBm",
    "outputId": "c1a686f8-163e-4ec1-ada1-3a4c92e42280"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+---+---------+----------+--------------------+\n",
      "|             address|age|firstName|  lastName|         phoneNumber|\n",
      "+--------------------+---+---------+----------+--------------------+\n",
      "|[New Orleans, LA,...| 22|     Jane|Butterburg|[210-332-578, mob...|\n",
      "|[Brighton, MI, 4 ...| 25|Josephine|   Darakjy|[210-319-103, mob...|\n",
      "|[Bridgeport, NJ, ...| 32|    Boris|    Chemel|[210-322-007, mob...|\n",
      "|[Bridgeport, NJ, ...| 35|  Johnson|     Smith|[210-303-029, mob...|\n",
      "+--------------------+---+---------+----------+--------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show()   #显示 df 的内容"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "DWlYgcxogcBm",
    "outputId": "52900972-0078-4599-9f44-86fb6fb9fb50"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(address=Row(city='New Orleans', state='LA', street='6649 N Blue Gum St', zip='70116'), age=22, firstName='Jane', lastName='Butterburg', phoneNumber=Row(number='210-332-578', type='mobile')),\n",
       " Row(address=Row(city='Brighton', state='MI', street='4 B Blue Ridge Blvd', zip='48116'), age=25, firstName='Josephine', lastName='Darakjy', phoneNumber=Row(number='210-319-103', type='mobile')),\n",
       " Row(address=Row(city='Bridgeport', state='NJ', street='8 W Cerritos Ave #54', zip='08014'), age=32, firstName='Boris', lastName='Chemel', phoneNumber=Row(number='210-322-007', type='mobile'))]"
      ]
     },
     "execution_count": 72,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(3)   #返回前 n 行数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "c_5XGuHZgcBm",
    "outputId": "210f27c6-d8f9-40f2-fd63-716c4ef73339"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Row(address=Row(city='New Orleans', state='LA', street='6649 N Blue Gum St', zip='70116'), age=22, firstName='Jane', lastName='Butterburg', phoneNumber=Row(number='210-332-578', type='mobile'))"
      ]
     },
     "execution_count": 73,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.first()   #返回第 1 行数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "6MNwne0cgcBm",
    "outputId": "beb4e0fb-9ee0-4a2f-bc7d-b63d51b142e7"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(address=Row(city='New Orleans', state='LA', street='6649 N Blue Gum St', zip='70116'), age=22, firstName='Jane', lastName='Butterburg', phoneNumber=Row(number='210-332-578', type='mobile')),\n",
       " Row(address=Row(city='Brighton', state='MI', street='4 B Blue Ridge Blvd', zip='48116'), age=25, firstName='Josephine', lastName='Darakjy', phoneNumber=Row(number='210-319-103', type='mobile')),\n",
       " Row(address=Row(city='Bridgeport', state='NJ', street='8 W Cerritos Ave #54', zip='08014'), age=32, firstName='Boris', lastName='Chemel', phoneNumber=Row(number='210-322-007', type='mobile'))]"
      ]
     },
     "execution_count": 74,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.take(3)   #返回前 n 行数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "HBaN-EjPgcBm",
    "outputId": "2928f5ef-190b-4e03-fc0a-8555b3d0b247"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "StructType(List(StructField(address,StructType(List(StructField(city,StringType,true),StructField(state,StringType,true),StructField(street,StringType,true),StructField(zip,StringType,true))),true),StructField(age,LongType,true),StructField(firstName,StringType,true),StructField(lastName,StringType,true),StructField(phoneNumber,StructType(List(StructField(number,StringType,true),StructField(type,StringType,true))),true)))"
      ]
     },
     "execution_count": 75,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.schema   #返回 df 的 Schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "vmXrvSQ9gcBm",
    "outputId": "efc7531d-4167-4f6c-d271-ad1e6cf00a8b"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-----------------+---------+----------+\n",
      "|summary|              age|firstName|  lastName|\n",
      "+-------+-----------------+---------+----------+\n",
      "|  count|                4|        4|         4|\n",
      "|   mean|             28.5|     null|      null|\n",
      "| stddev|6.027713773341708|     null|      null|\n",
      "|    min|               22|    Boris|Butterburg|\n",
      "|    max|               35|Josephine|     Smith|\n",
      "+-------+-----------------+---------+----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.describe().show()   #汇总统计数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "X_dNs0w1gcBn",
    "outputId": "c3c9dd58-2927-40f1-ed1e-0350e2d23e06"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['address', 'age', 'firstName', 'lastName', 'phoneNumber']"
      ]
     },
     "execution_count": 77,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns   #返回 df 的列名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "eG5H8-rEgcBn",
    "outputId": "162ea957-6f9b-4943-9e26-8324146067ca"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "execution_count": 78,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.count()   #返回 df 的行数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "Kd4sgihwgcBn",
    "outputId": "dfe2daef-8e80-4b73-8f75-2bfbc47e621b"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "execution_count": 79,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.distinct().count()   #返回 df 中不重复的行数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "ryU3p7YzgcBn",
    "outputId": "5eb3d676-15fb-4e13-f446-6ffb805554b2"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- address: struct (nullable = true)\n",
      " |    |-- city: string (nullable = true)\n",
      " |    |-- state: string (nullable = true)\n",
      " |    |-- street: string (nullable = true)\n",
      " |    |-- zip: string (nullable = true)\n",
      " |-- age: long (nullable = true)\n",
      " |-- firstName: string (nullable = true)\n",
      " |-- lastName: string (nullable = true)\n",
      " |-- phoneNumber: struct (nullable = true)\n",
      " |    |-- number: string (nullable = true)\n",
      " |    |-- type: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()   #返回 df的 Schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "NYXkHW0GgcBn",
    "outputId": "61cfa802-04d1-4ed5-d508-43d25f53a9fa"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "== Physical Plan ==\n",
      "*(1) FileScan json [address#452,age#453L,firstName#454,lastName#455,phoneNumber#456] Batched: false, Format: JSON, Location: InMemoryFileIndex[file:/content/customers.json], PartitionFilters: [], PushedFilters: [], ReadSchema: struct<address:struct<city:string,state:string,street:string,zip:string>,age:bigint,firstName:str...\n"
     ]
    }
   ],
   "source": [
    "df.explain()   #返回逻辑与实体方案"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "GZS-a6SHgcBn"
   },
   "source": [
    "## 重复值"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "OANWfrlBgcBn"
   },
   "source": [
    "**dropDuplicates函数**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "id": "4bHRKcH2gcBo"
   },
   "outputs": [],
   "source": [
    "df = df.dropDuplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "IehWd9P1gcBo"
   },
   "source": [
    "## 查询"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ZpKwzdVngcBo"
   },
   "source": [
    "**自定义函数F**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "id": "6KDWkEGJgcBo"
   },
   "outputs": [],
   "source": [
    "from pyspark.sql import functions as F"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "jJuMUF5VgcBo"
   },
   "source": [
    "### **Select**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "uscVmH2_gcBo",
    "outputId": "0cb93e3b-dd9a-4a61-a273-3530c30adc5f"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+\n",
      "|firstName|\n",
      "+---------+\n",
      "|Josephine|\n",
      "|     Jane|\n",
      "|    Boris|\n",
      "|  Johnson|\n",
      "+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(\"firstName\").show()   #显示 firstName 列的所有条目"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "bSPhZlxFgcBp",
    "outputId": "6100a6e5-7dd6-47d5-8148-5926fb1f41f7"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+----------+\n",
      "|firstName|  lastName|\n",
      "+---------+----------+\n",
      "|Josephine|   Darakjy|\n",
      "|     Jane|Butterburg|\n",
      "|    Boris|    Chemel|\n",
      "|  Johnson|     Smith|\n",
      "+---------+----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(\"firstName\",\"lastName\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "9fZI4O-usANE",
    "outputId": "b615aeac-730e-4fb0-9b77-6ab0bf16bec4"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+---+---------+----------+--------------------+\n",
      "|             address|age|firstName|  lastName|         phoneNumber|\n",
      "+--------------------+---+---------+----------+--------------------+\n",
      "|[Brighton, MI, 4 ...| 25|Josephine|   Darakjy|[210-319-103, mob...|\n",
      "|[New Orleans, LA,...| 22|     Jane|Butterburg|[210-332-578, mob...|\n",
      "|[Bridgeport, NJ, ...| 32|    Boris|    Chemel|[210-322-007, mob...|\n",
      "|[Bridgeport, NJ, ...| 35|  Johnson|     Smith|[210-303-029, mob...|\n",
      "+--------------------+---+---------+----------+--------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "tPY82HQPgcBp",
    "outputId": "1b367ba4-c67d-462d-92f3-54535a3a3a19"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+---------+---+\n",
      "|         contactInfo|firstName|age|\n",
      "+--------------------+---------+---+\n",
      "|[210-319-103, mob...|Josephine| 25|\n",
      "|[210-332-578, mob...|     Jane| 22|\n",
      "|[210-322-007, mob...|    Boris| 32|\n",
      "|[210-303-029, mob...|  Johnson| 35|\n",
      "+--------------------+---------+---+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(\"firstName\", \"age\", df.phoneNumber \\\n",
    "            .alias(\"contactInfo\")) \\\n",
    "            .select(\"contactInfo\", \"firstName\", \"age\") \\\n",
    "            .show()   #显示 firstName、age 的所有条目和类型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "n3y3efltgcBp",
    "outputId": "c729d00f-efa0-4e79-b08d-9b4716f45a13"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+---------+\n",
      "|firstName|(age + 1)|\n",
      "+---------+---------+\n",
      "|Josephine|       26|\n",
      "|     Jane|       23|\n",
      "|    Boris|       33|\n",
      "|  Johnson|       36|\n",
      "+---------+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(df[\"firstName\"],df[\"age\"]+ 1).show()   # 显示 firstName 和 age 列的所有 记录，并对 age 记录添加1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "ivy4Ma4-gcBp",
    "outputId": "239f3607-bf8a-4b3d-9bea-3254887ee891"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+\n",
      "|(age > 24)|\n",
      "+----------+\n",
      "|      true|\n",
      "|     false|\n",
      "|      true|\n",
      "|      true|\n",
      "+----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(df['age'] > 24).show()   #显示所有小于24岁的记录"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "dg-U8nr8gcBp"
   },
   "source": [
    "### **When**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "0WTVL9CtgcBq",
    "outputId": "b913390a-c556-4b51-eca0-67880fb62412"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+--------------------------------------+\n",
      "|firstName|CASE WHEN (age > 30) THEN 1 ELSE 0 END|\n",
      "+---------+--------------------------------------+\n",
      "|Josephine|                                     0|\n",
      "|     Jane|                                     0|\n",
      "|    Boris|                                     1|\n",
      "|  Johnson|                                     1|\n",
      "+---------+--------------------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(\"firstName\", F.when(df.age > 30, 1) \\\n",
    "            .otherwise(0)) \\\n",
    "            .show()   #显示 firstName，且大于30岁显示 1，小于30岁显示0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "mGEIn_I7gcBq",
    "outputId": "9db7df48-c09c-451b-85b5-5d2bada9d68c"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(address=Row(city='New Orleans', state='LA', street='6649 N Blue Gum St', zip='70116'), age=22, firstName='Jane', lastName='Butterburg', phoneNumber=Row(number='210-332-578', type='mobile')),\n",
       " Row(address=Row(city='Bridgeport', state='NJ', street='8 W Cerritos Ave #54', zip='08014'), age=32, firstName='Boris', lastName='Chemel', phoneNumber=Row(number='210-322-007', type='mobile'))]"
      ]
     },
     "execution_count": 96,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.firstName.isin(\"Jane\",\"Boris\")].collect()   # 显示符合指定条件的 firstName 列 的记录"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5szxA4DrgcBq"
   },
   "source": [
    "### **Like**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "tUhrTU_jgcBq",
    "outputId": "d5cdb99d-8434-4eba-fc5c-e3dadcdb9634"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+-------------------+\n",
      "|firstName|lastName LIKE Smith|\n",
      "+---------+-------------------+\n",
      "|Josephine|              false|\n",
      "|     Jane|              false|\n",
      "|    Boris|              false|\n",
      "|  Johnson|               true|\n",
      "+---------+-------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(\"firstName\", df.lastName.like(\"Smith\")) \\\n",
    "  .show()   # 显示 lastName 列中包含 Smith 的 firstName 列的记录"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "7Dg7xj9UgcBq"
   },
   "source": [
    "### **Startswith - Endswith**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "gZn7SWCIgcBq",
    "outputId": "c52e9a06-6c7d-4b2a-edd3-c74ea050626e"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+------------------------+\n",
      "|firstName|startswith(lastName, Sm)|\n",
      "+---------+------------------------+\n",
      "|Josephine|                   false|\n",
      "|     Jane|                   false|\n",
      "|    Boris|                   false|\n",
      "|  Johnson|                    true|\n",
      "+---------+------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(\"firstName\", df.lastName \\\n",
    "  .startswith(\"Sm\")) \\\n",
    "  .show()   # 显示 lastName 列中以 Sm 开头的 firstName 列的记录"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "0ySUrYiTgcBr",
    "outputId": "d2fac4c2-39b2-487f-d077-526060cacd83"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------------+\n",
      "|endswith(lastName, th)|\n",
      "+----------------------+\n",
      "|                 false|\n",
      "|                 false|\n",
      "|                 false|\n",
      "|                  true|\n",
      "+----------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(df.lastName \\\n",
    "  .endswith(\"th\")) \\\n",
    "  .show()   # 显示以 th 结尾的 lastName"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "hg8PEb9wgcBr"
   },
   "source": [
    "### **Substring**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "NLgXsd9fgcBr",
    "outputId": "9167f04b-9f4c-40a0-ca06-e12ccbdd1a23"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(name='Jos'), Row(name='Jan'), Row(name='Bor'), Row(name='Joh')]"
      ]
     },
     "execution_count": 100,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.select(df.firstName.substr(1, 3) \\\n",
    " .alias(\"name\")) \\\n",
    " .collect()   #返回 firstName 的子字符串"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "2mowFUJbgcBr"
   },
   "source": [
    "###  **Between**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "OEbKb9HdgcBr",
    "outputId": "53ac9099-2262-4b37-9b00-6101b2f91d33"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------------------+\n",
      "|((age >= 22) AND (age <= 24))|\n",
      "+-----------------------------+\n",
      "|                        false|\n",
      "|                         true|\n",
      "|                        false|\n",
      "|                        false|\n",
      "+-----------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(df.age.between(22, 24)) \\\n",
    "        .show()   #显示介于22岁至24岁之间的 age 列的记录"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "IcOdlc4ZgcBr"
   },
   "source": [
    "## 添加、修改、删除列"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "FphjTvE_gcBs"
   },
   "source": [
    "### 添加列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "AE7Z_iUgtJss",
    "outputId": "184ed345-0e65-49c4-ac16-0c99c43c06f6"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+\n",
      "|phoneNumber.number|\n",
      "+------------------+\n",
      "|       210-319-103|\n",
      "|       210-332-578|\n",
      "|       210-322-007|\n",
      "|       210-303-029|\n",
      "+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select(df.phoneNumber.number).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "id": "3S91EnoRgcBs"
   },
   "outputs": [],
   "source": [
    "df = df.withColumn('city',df.address.city) \\\n",
    "             .withColumn('postalCode',df.address.zip) \\\n",
    "             .withColumn('state',df.address.state) \\\n",
    "             .withColumn('streetAddress',df.address.street) \\\n",
    "             .withColumn('telePhoneNumber', df.phoneNumber.number) \\\n",
    "             .withColumn('telePhoneType', df.phoneNumber.type)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "eohau09XtfNA",
    "outputId": "50e5814f-eb7c-4d0c-ecdc-46429876eb78"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+---+---------+----------+--------------------+-----------+----------+-----+--------------------+---------------+-------------+\n",
      "|             address|age|firstName|  lastName|         phoneNumber|       city|postalCode|state|       streetAddress|telePhoneNumber|telePhoneType|\n",
      "+--------------------+---+---------+----------+--------------------+-----------+----------+-----+--------------------+---------------+-------------+\n",
      "|[Brighton, MI, 4 ...| 25|Josephine|   Darakjy|[210-319-103, mob...|   Brighton|     48116|   MI| 4 B Blue Ridge Blvd|    210-319-103|       mobile|\n",
      "|[New Orleans, LA,...| 22|     Jane|Butterburg|[210-332-578, mob...|New Orleans|     70116|   LA|  6649 N Blue Gum St|    210-332-578|       mobile|\n",
      "|[Bridgeport, NJ, ...| 32|    Boris|    Chemel|[210-322-007, mob...| Bridgeport|     08014|   NJ|8 W Cerritos Ave #54|    210-322-007|       mobile|\n",
      "|[Bridgeport, NJ, ...| 35|  Johnson|     Smith|[210-303-029, mob...| Bridgeport|     08112|   NJ|     5 W Blue Ave St|    210-303-029|       mobile|\n",
      "+--------------------+---+---------+----------+--------------------+-----------+----------+-----+--------------------+---------------+-------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "zCe618KIgcBs"
   },
   "source": [
    "### 修改列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "id": "I5zs8vk9gcBs"
   },
   "outputs": [],
   "source": [
    "df = df.withColumnRenamed('telePhoneNumber', 'phoneNumber')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "lwdlhvhLgcBs"
   },
   "source": [
    "### 删除列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {
    "id": "1mzCUlkYgcBt"
   },
   "outputs": [],
   "source": [
    "df = df.drop(\"address\", \"phoneNumber\")\n",
    "# 等价于\n",
    "# df = df.drop(df.address).drop(df.phoneNumber)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HjxC4rYlgcBt"
   },
   "source": [
    "## 分组"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "TJZVkbxKgcBt"
   },
   "source": [
    "**groupBy操作**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "mR2nRrWqgcBt",
    "outputId": "37236d01-e14f-40b9-fbbf-5b345f09a2f2"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+-----+\n",
      "|age|count|\n",
      "+---+-----+\n",
      "| 22|    1|\n",
      "| 32|    1|\n",
      "| 25|    1|\n",
      "| 35|    1|\n",
      "+---+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupBy(\"age\")\\\n",
    "      .count() \\\n",
    "      .show()   #按 age 列分组，统计每组人数"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "1RBeY2rigcBt"
   },
   "source": [
    "## 筛选"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "eLpm4uKpgcBu"
   },
   "source": [
    "**filter筛选**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "i5vpsAOagcBu",
    "outputId": "b2526d7a-4871-43c2-af9e-fdbd44457f9c"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---------+--------+----------+----------+-----+--------------------+-------------+\n",
      "|age|firstName|lastName|      city|postalCode|state|       streetAddress|telePhoneType|\n",
      "+---+---------+--------+----------+----------+-----+--------------------+-------------+\n",
      "| 25|Josephine| Darakjy|  Brighton|     48116|   MI| 4 B Blue Ridge Blvd|       mobile|\n",
      "| 32|    Boris|  Chemel|Bridgeport|     08014|   NJ|8 W Cerritos Ave #54|       mobile|\n",
      "| 35|  Johnson|   Smith|Bridgeport|     08112|   NJ|     5 W Blue Ave St|       mobile|\n",
      "+---+---------+--------+----------+----------+-----+--------------------+-------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.filter(df[\"age\"]>24).show()   #按 age 列筛选，保留年龄大于24岁的"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "qNhkr48ugcBu"
   },
   "source": [
    "## 排序"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "hkm8pSbOgcBu"
   },
   "source": [
    "**sort与orderBy操作**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "rPm50gDXgcBu",
    "outputId": "01084ab8-fa01-4d0c-b21b-37fc1697f91c"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(age=30, name='Andy'),\n",
       " Row(age=29, name='Michael'),\n",
       " Row(age=19, name='Justin')]"
      ]
     },
     "execution_count": 114,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "peopledf.sort(peopledf.age.desc()).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "R7UTXjuPgcBu",
    "outputId": "9130eb46-fb2e-4e3f-8d0b-61776c222c2a"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(age=35, firstName='Johnson', lastName='Smith', city='Bridgeport', postalCode='08112', state='NJ', streetAddress='5 W Blue Ave St', telePhoneType='mobile'),\n",
       " Row(age=32, firstName='Boris', lastName='Chemel', city='Bridgeport', postalCode='08014', state='NJ', streetAddress='8 W Cerritos Ave #54', telePhoneType='mobile'),\n",
       " Row(age=25, firstName='Josephine', lastName='Darakjy', city='Brighton', postalCode='48116', state='MI', streetAddress='4 B Blue Ridge Blvd', telePhoneType='mobile'),\n",
       " Row(age=22, firstName='Jane', lastName='Butterburg', city='New Orleans', postalCode='70116', state='LA', streetAddress='6649 N Blue Gum St', telePhoneType='mobile')]"
      ]
     },
     "execution_count": 115,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort(\"age\", ascending=False).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "Xo8luhnBgcBv",
    "outputId": "b55215ba-1dc3-42db-cadf-9ea8dc7c5658"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(age=35, firstName='Johnson', lastName='Smith', city='Bridgeport', postalCode='08112', state='NJ', streetAddress='5 W Blue Ave St', telePhoneType='mobile'),\n",
       " Row(age=32, firstName='Boris', lastName='Chemel', city='Bridgeport', postalCode='08014', state='NJ', streetAddress='8 W Cerritos Ave #54', telePhoneType='mobile'),\n",
       " Row(age=25, firstName='Josephine', lastName='Darakjy', city='Brighton', postalCode='48116', state='MI', streetAddress='4 B Blue Ridge Blvd', telePhoneType='mobile'),\n",
       " Row(age=22, firstName='Jane', lastName='Butterburg', city='New Orleans', postalCode='70116', state='LA', streetAddress='6649 N Blue Gum St', telePhoneType='mobile')]"
      ]
     },
     "execution_count": 116,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.orderBy([\"age\",\"city\"],ascending=[0,1]).collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "2pxFJhsVgcBv"
   },
   "source": [
    "## 替换缺失值"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "2GrWVqOhgcBv"
   },
   "source": [
    "**replace操作**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "5UdKCa6CgcBv",
    "outputId": "185772c1-4b60-411c-cad4-1f33032ed890"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---------+----------+-----------+----------+-----+--------------------+-------------+\n",
      "|age|firstName|  lastName|       city|postalCode|state|       streetAddress|telePhoneType|\n",
      "+---+---------+----------+-----------+----------+-----+--------------------+-------------+\n",
      "| 25|Josephine|   Darakjy|   Brighton|     48116|   MI| 4 B Blue Ridge Blvd|       mobile|\n",
      "| 22|     Jane|Butterburg|New Orleans|     70116|   LA|  6649 N Blue Gum St|       mobile|\n",
      "| 32|    Boris|    Chemel| Bridgeport|     08014|   NJ|8 W Cerritos Ave #54|       mobile|\n",
      "| 35|  Johnson|     Smith| Bridgeport|     08112|   NJ|     5 W Blue Ave St|       mobile|\n",
      "+---+---------+----------+-----------+----------+-----+--------------------+-------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.na.fill(50).show()   #用一个值替换空值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "SLmC8DFKgcBv",
    "outputId": "a336d0d2-8315-40b2-f632-c592b170d5ec"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---------+----------+-----------+----------+-----+--------------------+-------------+\n",
      "|age|firstName|  lastName|       city|postalCode|state|       streetAddress|telePhoneType|\n",
      "+---+---------+----------+-----------+----------+-----+--------------------+-------------+\n",
      "| 25|Josephine|   Darakjy|   Brighton|     48116|   MI| 4 B Blue Ridge Blvd|       mobile|\n",
      "| 22|     Jane|Butterburg|New Orleans|     70116|   LA|  6649 N Blue Gum St|       mobile|\n",
      "| 32|    Boris|    Chemel| Bridgeport|     08014|   NJ|8 W Cerritos Ave #54|       mobile|\n",
      "| 35|  Johnson|     Smith| Bridgeport|     08112|   NJ|     5 W Blue Ave St|       mobile|\n",
      "+---+---------+----------+-----------+----------+-----+--------------------+-------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.na.drop().show()   #去除 df 中为空值的行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "sFe0IkkWgcBv",
    "outputId": "549d1ae1-c061-45d4-ba94-e1c1b8991d14"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---------+----------+-----------+----------+-----+--------------------+-------------+\n",
      "|age|firstName|  lastName|       city|postalCode|state|       streetAddress|telePhoneType|\n",
      "+---+---------+----------+-----------+----------+-----+--------------------+-------------+\n",
      "| 25|Josephine|   Darakjy|   Brighton|     48116|   MI| 4 B Blue Ridge Blvd|       mobile|\n",
      "| 22|     Jane|Butterburg|New Orleans|     70116|   LA|  6649 N Blue Gum St|       mobile|\n",
      "| 32|    Boris|    Chemel| Bridgeport|     08014|   NJ|8 W Cerritos Ave #54|       mobile|\n",
      "| 35|  Johnson|     Smith| Bridgeport|     08112|   NJ|     5 W Blue Ave St|       mobile|\n",
      "+---+---------+----------+-----------+----------+-----+--------------------+-------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.na.replace(10, 20).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "CGWi4Z9ngcBv"
   },
   "source": [
    "## 重分区"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "0eeL-EWOgcBw"
   },
   "source": [
    "**repartition重分区**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "tbaDFvgbgcBw",
    "outputId": "864ab25a-a69b-41e7-d26e-9cb6f22014de"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10"
      ]
     },
     "execution_count": 120,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.repartition(10)\\\n",
    "      .rdd \\\n",
    "      .getNumPartitions()   #将 df 拆分为10个分区"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "NqTZQ5nQgcBw",
    "outputId": "ab14b965-0ead-4e7c-cd45-5a46e2288935"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 121,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.coalesce(1).rdd.getNumPartitions()   #将 df 合并为1个分区"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "YlVNbJWegcBw"
   },
   "source": [
    "## 运行 SQL 查询"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "oZ3IDSlPgcBx"
   },
   "source": [
    "### 将数据帧注册为视图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {
    "id": "azUe-5OTgcBx"
   },
   "outputs": [],
   "source": [
    "peopledf.createGlobalTempView(\"people\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "id": "Bx3tYmDYgcBx"
   },
   "outputs": [],
   "source": [
    "df.createTempView(\"customer\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {
    "id": "-mX0UfxjgcBx"
   },
   "outputs": [],
   "source": [
    "df.createOrReplaceTempView(\"customer\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "FCLvtoL7gcBx"
   },
   "source": [
    "### 查询视图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "iVnnP4-AgcBx",
    "outputId": "0e69c7c1-32c8-45fb-f0cb-32bdaec062fb"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---------+----------+-----------+----------+-----+--------------------+-------------+\n",
      "|age|firstName|  lastName|       city|postalCode|state|       streetAddress|telePhoneType|\n",
      "+---+---------+----------+-----------+----------+-----+--------------------+-------------+\n",
      "| 25|Josephine|   Darakjy|   Brighton|     48116|   MI| 4 B Blue Ridge Blvd|       mobile|\n",
      "| 22|     Jane|Butterburg|New Orleans|     70116|   LA|  6649 N Blue Gum St|       mobile|\n",
      "| 32|    Boris|    Chemel| Bridgeport|     08014|   NJ|8 W Cerritos Ave #54|       mobile|\n",
      "| 35|  Johnson|     Smith| Bridgeport|     08112|   NJ|     5 W Blue Ave St|       mobile|\n",
      "+---+---------+----------+-----------+----------+-----+--------------------+-------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df5 = spark.sql(\"SELECT * FROM customer\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "TAEfjbz3gcBy",
    "outputId": "ebb60bf2-849e-46bf-80f9-ea1b651fa36a"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+-------+\n",
      "|age|   name|\n",
      "+---+-------+\n",
      "| 29|Michael|\n",
      "| 30|   Andy|\n",
      "| 19| Justin|\n",
      "+---+-------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "peopledf2 = spark.sql(\"SELECT * FROM global_temp.people\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "vd6qop7mgcBy"
   },
   "source": [
    "## 输出"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "0D5UH0jFgcBy"
   },
   "source": [
    "### 数据结构"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {
    "id": "DgZaeOHfgcBy"
   },
   "outputs": [],
   "source": [
    "rdd1 = df.rdd   #将 df 转换为 RDD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 54
    },
    "id": "OLfh6ZxlgcBy",
    "outputId": "056246e3-4e24-4fb9-db4a-114be523aeec"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'{\"age\":25,\"firstName\":\"Josephine\",\"lastName\":\"Darakjy\",\"city\":\"Brighton\",\"postalCode\":\"48116\",\"state\":\"MI\",\"streetAddress\":\"4 B Blue Ridge Blvd\",\"telePhoneType\":\"mobile\"}'"
      ]
     },
     "execution_count": 128,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.toJSON().first()   #将 df 转换为 RDD 字符串"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 175
    },
    "id": "Q2wXSgLEgcBy",
    "outputId": "53188bcd-db98-4c77-9794-018c0101d787"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>age</th>\n",
       "      <th>firstName</th>\n",
       "      <th>lastName</th>\n",
       "      <th>city</th>\n",
       "      <th>postalCode</th>\n",
       "      <th>state</th>\n",
       "      <th>streetAddress</th>\n",
       "      <th>telePhoneType</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25</td>\n",
       "      <td>Josephine</td>\n",
       "      <td>Darakjy</td>\n",
       "      <td>Brighton</td>\n",
       "      <td>48116</td>\n",
       "      <td>MI</td>\n",
       "      <td>4 B Blue Ridge Blvd</td>\n",
       "      <td>mobile</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22</td>\n",
       "      <td>Jane</td>\n",
       "      <td>Butterburg</td>\n",
       "      <td>New Orleans</td>\n",
       "      <td>70116</td>\n",
       "      <td>LA</td>\n",
       "      <td>6649 N Blue Gum St</td>\n",
       "      <td>mobile</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>32</td>\n",
       "      <td>Boris</td>\n",
       "      <td>Chemel</td>\n",
       "      <td>Bridgeport</td>\n",
       "      <td>08014</td>\n",
       "      <td>NJ</td>\n",
       "      <td>8 W Cerritos Ave #54</td>\n",
       "      <td>mobile</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>35</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>Smith</td>\n",
       "      <td>Bridgeport</td>\n",
       "      <td>08112</td>\n",
       "      <td>NJ</td>\n",
       "      <td>5 W Blue Ave St</td>\n",
       "      <td>mobile</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   age  firstName    lastName  ... state         streetAddress telePhoneType\n",
       "0   25  Josephine     Darakjy  ...    MI   4 B Blue Ridge Blvd        mobile\n",
       "1   22       Jane  Butterburg  ...    LA    6649 N Blue Gum St        mobile\n",
       "2   32      Boris      Chemel  ...    NJ  8 W Cerritos Ave #54        mobile\n",
       "3   35    Johnson       Smith  ...    NJ       5 W Blue Ave St        mobile\n",
       "\n",
       "[4 rows x 8 columns]"
      ]
     },
     "execution_count": 129,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.toPandas()   #将 df 的内容转为 Pandas 的数据帧"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "tzBh9HWPgcBz"
   },
   "source": [
    "### 保存至文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {
    "id": "4MzUnwzBgcBz"
   },
   "outputs": [],
   "source": [
    "df.select(\"firstName\", \"city\")\\\n",
    "      .write \\\n",
    "      .save(\"nameAndCity.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {
    "id": "qqmhu-wsgcBz"
   },
   "outputs": [],
   "source": [
    "df.select(\"firstName\", \"age\") \\\n",
    "      .write \\\n",
    "      .save(\"namesAndAges.json\",format=\"json\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "RuUOQPx6gcBz"
   },
   "source": [
    "## 终止SparkSession"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "guLQqLi2gcBz"
   },
   "source": [
    "**终止spark session**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ocQ8ikrbgcBz"
   },
   "outputs": [],
   "source": [
    "spark.stop()"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "Spark_SQL_cheatsheet_code.ipynb",
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
